create sequence population_id_seq
    as integer;

alter sequence population_id_seq owner to postgres;

create sequence equipment_id_seq
    as integer;

alter sequence equipment_id_seq owner to postgres;

create table role
(
    id        serial
        primary key,
    role_name varchar(50) not null,
    access    varchar(100)
);

alter table role
    owner to postgres;

create table users
(
    id          serial
        primary key,
    last_name   varchar(50)  not null,
    first_name  varchar(50)  not null,
    middle_name varchar(50),
    email       varchar(100) not null
        unique,
    auth_token  varchar(100),
    role_id     integer
        references role,
    password    varchar(200),
    last_login  timestamp
);

alter table users
    owner to postgres;

create table types
(
    id    serial
        primary key,
    tname varchar(50) not null
);

alter table types
    owner to postgres;

create table subtypes
(
    id      serial
        primary key,
    name    varchar(50) not null,
    type_id integer
        references types
);

alter table subtypes
    owner to postgres;

create table modes
(
    id    serial
        primary key,
    mname varchar(50) not null
);

alter table modes
    owner to postgres;

create table equipment
(
    eid         integer default nextval('equipment_id_seq'::regclass) not null
        primary key,
    ename       varchar(50)                                           not null,
    id_mode     integer
        references modes,
    id_subtypes integer                                               not null
        constraint equipment_subtypes_id_fk
            references subtypes,
    free boolean default true,
    off boolean default true,
    code varchar(50),
    comment text
);

alter table equipment
    owner to postgres;

alter sequence equipment_id_seq owned by equipment.eid;

create table population
(
    pid          integer default nextval('population_id_seq'::regclass) not null
        primary key,
    pcode VARCHAR(50) UNIQUE NOT NULL,
    pname VARCHAR(50) NOT NULL,
    startdate DATE,
    enddate DATE DEFAULT NULL,
    active boolean DEFAULT TRUE NOT NULL,
    equipment_id INTEGER REFERENCES equipment,
    avg_start_mass DECIMAL(10, 2) NOT NULL,
    avg_start_size DECIMAL(10, 2) NOT NULL,
    start_count INTEGER NOT NULL,
    start_population_mass DECIMAL(10, 2),
    comment text
);

alter table population
    owner to postgres;

alter sequence population_id_seq owned by population.pid;

create table sensors
(
    id                      serial
        primary key,
    name                    varchar(50) not null,
    location                varchar(50),
    dimension  varchar(50),
    min_operating_condition double precision,
    max_operating_condition double precision,
    min_critical_condition  double precision,
    max_critical_condition  double precision,
    min_fatal_condition     double precision,
    max_fatal_condition     double precision,
    units                   varchar(20),
    frequency               varchar,
    on_off                  boolean,
    id_equipment            integer
        references equipment,
        comment text
);

alter table sensors
    owner to postgres;

create table indications
(
    id         serial
        primary key,
    value      double precision,
    user_id    integer,
    id_sensors integer
        references sensors,
    date_time  timestamp,
    comment text
);

alter table indications
    owner to postgres;

create table populations_growth
(
    id                    serial
        primary key,
    number_of_individuals integer,
    mass_of_individuals   double precision,
    id_population         integer
        references population,
    date_time             timestamp,
    average_mass    decimal(10, 2),
    average_size decimal(10, 2),
    mortality decimal(10, 2)
);

alter table populations_growth
    owner to postgres;

create table food
(
    fid         integer not null
        primary key,
    name        varchar,
    composition varchar
);

alter table food
    owner to postgres;

create table food_populations
(
    id               serial
        primary key,
    amount           double precision,
    food_brand       varchar(50),
    food_batch       varchar(50),
    food_size varchar(50),
    id_population    integer
        references population,
    feeding_time     timestamp,
    food_composition text
);

alter table food_populations
    owner to postgres;

create table redistributions
(
    redistribution_id    serial
        primary key,
    source_population_id integer
        references population,
    target_population_id integer
        references population,
    redistribution_date  date not null,
    reason               varchar(255)
);
create table logs
(
    id            serial not null
        constraint logs_pk
            primary key,
    population_id integer,
    pools   integer,
    date_time_start     timestamp,
    date_time_end     timestamp

);

alter table redistributions
    owner to postgres;

create index idx_redistribution_date
    on redistributions (redistribution_date);

INSERT INTO role (id, role_name, access)
VALUES (DEFAULT, 'Admin', 'Все права');

INSERT INTO role (id, role_name, access)
VALUES (DEFAULT, 'User', 'Не все права');

INSERT INTO users (id, last_name, first_name, middle_name, email, auth_token, role_id, password, last_login)
VALUES (DEFAULT, 'Пользователь', 'Пользователь', 'Пользователь', 'mail@example.ru', null, 1,
        '$2a$10$R64YGIB2m492FwLWBBXMp.O0sJA3N0JSCkpW.IjTwQLbPcGVpurmq', null);

INSERT INTO modes (id, mname) VALUES (DEFAULT, 'В штатном режиме');

INSERT INTO modes (id, mname) VALUES (DEFAULT, 'Максимальная нагрузка');

INSERT INTO modes (id, mname) VALUES (DEFAULT, 'Отключен');
